package aware.util.dao.oracle;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Ref;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Struct;
import java.util.ArrayList;
import java.sql.Types;


import oracle.jdbc.OracleTypes;

import aware.util.dao.oracle.OracleDAOFactory;
import aware.util.dao.oracle.OracleStoredProcedure;
import aware.util.dao.oracle.OracleStoredProcedureParameter;
import aware.util.dao.DaoUtil;
import aware.util.dao.SQLStoredProcedureExecutor;

public class OracleStoredProcedureExecutor implements
		SQLStoredProcedureExecutor {
	/*
	 * Must match the catalog name as it is stored in the database; "" retrieves
	 * those without a catalog; null means that the catalog name should not be
	 * used to narrow the search
	 */
	public final String catalog;

	/*
	 * Must match the schema name as it is stored in the database; "" retrieves
	 * those without a schema; null means that the schema name should not be
	 * used to narrow the search.
	 */
	public final String schemaPattern;

	/*
	 * Must match the procedure name as it is stored in the database.
	 */
	public final String procedureNamePattern;

	/* The connection object. */
	private final Connection conn;

	/*
	 * The CallableStatement object for this procedure.
	 */
	private final CallableStatement csmt;

	/*
	 * Oracle Procedure info.
	 */
	private OracleStoredProcedure osp;

	public static int noOfParameters;
	
	public OracleStoredProcedureExecutor(String procedureNamePattern) {

		this("", "", procedureNamePattern);

	}

	public OracleStoredProcedureExecutor(String catalog,
			String procedureNamePattern) {

		this(catalog, "", procedureNamePattern);

	}

	public OracleStoredProcedureExecutor(String catalog, String schemaPattern,
			String procedureNamePattern) {
		this.catalog = catalog;
		this.schemaPattern = schemaPattern;
		this.procedureNamePattern = procedureNamePattern;
		this.conn = OracleDAOFactory.createConnection();
		
		try {
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.csmt = getCallableStatement();

	}

	private CallableStatement getCallableStatement() {
		CallableStatement csmt = null;
		DatabaseMetaData dbmd = null;
		ResultSet procedureRS = null;
		ResultSet procedureColumnRS = null;
		int noOfProcedureStatus = 0; // 0 -> no such procedure
		// 1 -> one procedure
		// 2 -> overloaded procedure exists in database.
		try {
			dbmd = (DatabaseMetaData)conn.getMetaData();
			
			procedureRS = dbmd.getProcedures(catalog, schemaPattern,
					procedureNamePattern);
			String procedureCat = null;
			String procedureSchem = null;
			String procedureName = null;
			String remarks = null;
			short procedureType = -1;
			if (!procedureRS.next()) {
				noOfProcedureStatus = 0;
			} else {
				procedureCat = procedureRS.getString("PROCEDURE_CAT");
				procedureSchem = procedureRS.getString("PROCEDURE_SCHEM");
				procedureName = procedureRS.getString("PROCEDURE_NAME");
				remarks = procedureRS.getString("REMARKS");
				procedureType = procedureRS.getShort("PROCEDURE_TYPE");
				if (procedureRS.next()) {
					noOfProcedureStatus = 2;
				} else {
					noOfProcedureStatus = 1;
				}
			}
			DaoUtil.close(procedureRS);
			procedureRS = null;
			String storedProcedureName = ((catalog == null || catalog
					.equals("")) ? "" : (catalog + "."))
					+ procedureNamePattern;
			if (noOfProcedureStatus == 0) {

				throw new RuntimeException("The procedure '"
						+ storedProcedureName + "' doesn't exist");
			} else if (noOfProcedureStatus == 1) {
				ArrayList<OracleStoredProcedureParameter> parameterList = new ArrayList<OracleStoredProcedureParameter>();
				StringBuffer sqlSB = new StringBuffer();

				osp = new OracleStoredProcedure();

				osp.setParameterList(parameterList);
				osp.setProcedureCat(procedureCat);
				osp.setProcedureName(procedureName);
				osp.setProcedureSchem(procedureSchem);
				osp.setProcedureType(procedureType);
				osp.setRemarks(remarks);

				switch (osp.getProcedureType()) {
				case 0:
					throw new RuntimeException("Procedure Result is Unknown");
				case 1:
					// Procedure with no result
					sqlSB.append("{ call " + storedProcedureName + " ( ");
					break;
				case 2:
					// Procedure returns result
					sqlSB.append("{ ? = call " + storedProcedureName + " ( ");
					break;
				}

				procedureColumnRS = dbmd.getProcedureColumns(catalog,
						schemaPattern, procedureNamePattern, null);

				int parameterCount = 0;
				while (procedureColumnRS.next()) {

					OracleStoredProcedureParameter parameter = new OracleStoredProcedureParameter();

					parameter.setColumnName(procedureColumnRS
							.getString("COLUMN_NAME"));
					parameter.setColumnType(procedureColumnRS
							.getShort("COLUMN_TYPE"));
					parameter
							.setDataType(procedureColumnRS.getInt("DATA_TYPE"));
					parameter.setLength(procedureColumnRS.getInt("LENGTH"));
					parameter.setNullable(procedureColumnRS
							.getShort("NULLABLE"));
					parameter.setPrecision(procedureColumnRS
							.getInt("PRECISION"));
					parameter.setRadix(procedureColumnRS.getShort("RADIX"));
					parameter
							.setRemarks(procedureColumnRS.getString("REMARKS"));
					parameter.setScale(procedureColumnRS.getShort("SCALE"));
					parameter.setTypeName(procedureColumnRS
							.getString("TYPE_NAME"));

					++parameterCount;
					if (parameterCount > 1) {
						sqlSB.append(" , ");
					}

					sqlSB.append("?");

					parameterList.add(parameter);
				}
				sqlSB.append(" ) }");
				csmt = conn.prepareCall(sqlSB.toString());
				System.out.println("No of parameter:"+parameterCount);
			} else {
				throw new RuntimeException(storedProcedureName
						+ " is overloaded. "
						+ "This is applicable only for non overloaded Oracle "
						+ "function or procedures.");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return csmt;
	}

	/*
	 * public void execute() throws SQLException {
	 * 
	 * try {
	 * 
	 * if (!procedureRS.next()) { throw new RuntimeException("There is no
	 * procedure with name: " + this.packageName + "." + super.getBlockName()); }
	 * else {
	 * 
	 * procedureColumnRS = dbmd.getProcedureColumns(this.packageName, "",
	 * super.getBlockName(), null);
	 * 
	 * StringBuffer sqlSB = new StringBuffer("{ call " + this.packageName + "." +
	 * super.getBlockName() + " ( "); int parameterCount = 0; ArrayList<OracleStoredProcedureParameter>
	 * parameterList = new ArrayList<OracleStoredProcedureParameter>(); while
	 * (procedureColumnRS.next()) { OracleStoredProcedureParameter parameter =
	 * new OracleStoredProcedureParameter(); System.out.print("COLUMN_NAME:" +
	 * procedureColumnRS.getString("COLUMN_NAME"));
	 * parameter.setParameterName(procedureColumnRS .getString("COLUMN_NAME"));
	 * System.out.print("\t\t\tCOLUMN_TYPE:" +
	 * procedureColumnRS.getShort("COLUMN_TYPE"));
	 * parameter.setParameterType(procedureColumnRS .getShort("COLUMN_TYPE"));
	 * System.out.println("\tDATA_TYPE:" +
	 * procedureColumnRS.getInt("DATA_TYPE"));
	 * parameter.setParameterDataType(procedureColumnRS .getInt("DATA_TYPE"));
	 * System.out.println("\tTYPE_NAME:" +
	 * procedureColumnRS.getString("TYPE_NAME"));
	 * parameter.setParameterTypeName(procedureColumnRS
	 * .getString("TYPE_NAME")); ++parameterCount; if (parameterCount > 1) {
	 * sqlSB.append(" , "); } sqlSB.append("?"); parameterList.add(parameter); }
	 * sqlSB.append(" ) }");
	 * 
	 * if (procedureRS.next()) { throw new RuntimeException( this.packageName +
	 * "." + super.getBlockName() + " is overloaded. This is applicable only for
	 * non overloaded Oracle function or procedures."); } String sql =
	 * sqlSB.toString().trim(); System.out.println("SQL:" + sql); csmt =
	 * conn.prepareCall(sql); ArrayList<OracleStoredProcedureParameter>
	 * outParameterList = new ArrayList<OracleStoredProcedureParameter>(); for
	 * (OracleStoredProcedureParameter parameter : parameterList) { String
	 * parameterName = parameter.getParameterName(); short parameterType =
	 * parameter.getParameterType(); int parameterDataType =
	 * parameter.getParameterDataType(); String parameterDataTypeName =
	 * parameter .getParameterTypeName();
	 * 
	 * if (parameterType == 4) { // Out Parameter
	 * outParameterList.add(parameter); if (parameterDataType == 1111) { //
	 * System.out.println("Parameter type name:"+); if ("REF
	 * CURSOR".equals(parameterDataTypeName)) {
	 * csmt.registerOutParameter(parameterName, OracleTypes.CURSOR); } else {
	 * throw new RuntimeException("The oracle type '" + parameterDataTypeName + "'
	 * not supported."); } } else { csmt.registerOutParameter(parameterName,
	 * parameterDataType); } } else if (parameterType == 1) { // in parameter
	 * Object obj = getObject(parameter, dto);
	 * csmt.setObject(parameter.getParameterName(), obj,
	 * parameter.getParameterDataType()); } } System.out.println("SQL:" +
	 * csmt.toString()); csmt.execute(); for (OracleStoredProcedureParameter
	 * parameter : outParameterList) { String parameterName =
	 * parameter.getParameterName(); short parameterType =
	 * parameter.getParameterType(); int parameterDataType =
	 * parameter.getParameterDataType(); String parameterDataTypeName =
	 * parameter .getParameterTypeName(); Object obj = null; if
	 * (parameterDataType == 1111) { if ("REF
	 * CURSOR".equals(parameterDataTypeName)) { obj =
	 * csmt.getObject(parameterName); } else { throw new RuntimeException("The
	 * oracle type '" + parameterDataTypeName + "' not supported."); } } else {
	 * obj = csmt.getObject(parameterName); } setObject(parameter, dto, obj); } } }
	 * finally { DaoUtil.close(procedureRS); DaoUtil.close(procedureColumnRS);
	 * DaoUtil.close(conn, csmt); } }
	 */

	private Object getObject(OracleStoredProcedureParameter parameter,
			Object dto) {
		Object obj = null;
		String parameterName = parameter.getColumnName();
		Class<?> myClass = dto.getClass();
		Method[] methods = myClass.getMethods();
		ArrayList<String> methoNames = new ArrayList<String>();
		for (Method m : methods) {
			String methodName = m.getName();
			if ("get".equals(methodName.substring(0, 3))) {
				String dbParameterName = methodName.replaceFirst("get", "");
				if (dbParameterName.equalsIgnoreCase(parameterName)) {
					methoNames.add(methodName);
				}

			}
		}
		if (methoNames.size() == 0) {
			throw new RuntimeException(
					"Your DTO does not contain getter method for in parameter '"
							+ parameterName + "'");
		} else if (methoNames.size() == 1) {

			Class<?> cla = null;
			try {
				cla = Class.forName(myClass.getName());
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			Class<?> partypes[] = null;
			Method method = null;
			try {
				method = cla.getMethod(methoNames.get(0), partypes);
			} catch (SecurityException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (NoSuchMethodException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			Object arg[] = null;
			try {
				obj = method.invoke(dto, arg);
			} catch (IllegalArgumentException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		} else {
			throw new RuntimeException(
					"SQL is case insensitive. Your DTO has more than one getter method for '"
							+ parameterName + "'");
		}

		return obj;
	}

	private void setObject(OracleStoredProcedureParameter parameter,
			Object dto, Object obj) {
		String parameterName = parameter.getColumnName();
		int parameterDataType = parameter.getDataType();
		String parameterDataTypeName = parameter.getTypeName();
		Class<?> myClass = dto.getClass();
		Method[] methods = myClass.getMethods();
		ArrayList<String> methoNames = new ArrayList<String>();
		Class<?> javaClass = null;
		switch (parameterDataType) {
		case Types.ARRAY:
			javaClass = Array.class;
			break;
		case Types.BIGINT:
			javaClass = Long.class;
			break;
		case Types.BINARY:
			javaClass = byte[].class;
			break;
		case Types.BIT:
			javaClass = Boolean.class;
			break;
		case Types.BLOB:
			javaClass = java.sql.Blob.class;
			break;
		case Types.BOOLEAN:
			break;
		case Types.CHAR:
			javaClass = String.class;
			break;
		case Types.CLOB:
			javaClass = java.sql.Clob.class;
			break;
		case Types.DATALINK:
			break;
		case Types.DATE:
			javaClass = java.sql.Date.class;
			break;
		case Types.DECIMAL:
			javaClass = java.math.BigDecimal.class;
			break;
		case Types.DISTINCT:
			break;
		case Types.DOUBLE:
			javaClass = Double.class;
			break;
		case Types.FLOAT:
			javaClass = Double.class;
			break;
		case Types.INTEGER:
			javaClass = Integer.class;
			break;
		case Types.JAVA_OBJECT:
			break;
		case Types.LONGNVARCHAR:
			javaClass = String.class;
			break;
		case Types.LONGVARBINARY:
			javaClass = byte[].class;
			break;
		case Types.LONGVARCHAR:
			javaClass = String.class;
			break;
		case Types.NCHAR:
			break;
		case Types.NCLOB:
			break;
		case Types.NULL:
			break;
		case Types.NUMERIC:
			javaClass = java.math.BigDecimal.class;
			break;
		case Types.NVARCHAR:
			break;
		case Types.OTHER:
			if ("REF CURSOR".equalsIgnoreCase(parameterDataTypeName)) {
				javaClass = ResultSet.class;
			}
			break;
		case Types.REAL:
			javaClass = Float.class;
			break;
		case Types.REF:
			javaClass = Ref.class;
			break;
		case Types.ROWID:
			break;
		case Types.SMALLINT:
			javaClass = Integer.class;
			break;
		case Types.SQLXML:
			break;
		case Types.STRUCT:
			javaClass = Struct.class;
			break;
		case Types.TIME:
			javaClass = java.sql.Time.class;
			break;
		case Types.TIMESTAMP:
			javaClass = java.sql.Timestamp.class;
			break;
		case Types.TINYINT:
			javaClass = Integer.class;
			break;
		case Types.VARBINARY:
			javaClass = byte[].class;
			break;
		case Types.VARCHAR:
			javaClass = String.class;
			break;
		}
		if (javaClass == null) {

			throw new RuntimeException("'" + parameterDataTypeName
					+ "' not supported.");

		}
		for (Method m : methods) {
			String methodName = m.getName();
			Class<?>[] methodParameterTypes = m.getParameterTypes();
			if ("set".equals(methodName.substring(0, 3))
					&& methodParameterTypes.length == 1) {
				String dbParameterName = methodName.replaceFirst("set", "");
				if (dbParameterName.equalsIgnoreCase(parameterName)) {
					methoNames.add(methodName);
				}

			}
		}
		if (methoNames.size() == 0) {

			throw new RuntimeException("Setter method doesn't exist for "
					+ parameterName);

		} else if (methoNames.size() == 1) {
			Object[] input = { obj };
			Class<?> cl = null;
			try {
				cl = Class.forName(myClass.getName());
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			Class<?>[] par = { javaClass };
			Method mthd = null;
			try {
				mthd = cl.getMethod(methoNames.get(0), par);
			} catch (SecurityException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (NoSuchMethodException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				mthd.invoke(dto, input);
			} catch (IllegalArgumentException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (InvocationTargetException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} else {
			throw new RuntimeException(
					"SQL is case insensitive. Your DTO has more than one setter method for '"
							+ parameterName
							+ "'.For this db parameter, overloaded setter with on parameter in dto is not permitted.");
		}

	}

	public void test(Object obj) {
		Class<?> myClass = obj.getClass();
		Method[] methods = myClass.getMethods();
		for (Method m : methods) {
			System.out.println(m.getName());
			Class<?>[] parameterTypes = m.getParameterTypes();
			for (Class<?> c : parameterTypes) {
				System.out.println("\t" + c.getName());
			}
		}

	}

	@Override
	public void commit() {
		try {
			if (conn != null) {
				conn.commit();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public void execute(Object dto) throws SQLException {
		ArrayList<OracleStoredProcedureParameter> outParameterList = new ArrayList<OracleStoredProcedureParameter>();
		for (OracleStoredProcedureParameter parameter : osp.getParameterList()) {

			// String parameterName = parameter.getColumnName();
			short parameterType = parameter.getColumnType();
			// int parameterDataType = parameter.getDataType();
			// String parameterDataTypeName = parameter.getTypeName();

			switch (parameterType) {

			case 0:
				throw new RuntimeException("Unknown type procedure column.");
			case 1:
				// in parameter.
				setInParameter(parameter, dto);
				break;
			case 2:
				// inout parameter.
				setInOutParameter(parameter, dto);
				break;
			case 3:
				// result parameter in result set.
				setResultParameter(parameter, dto);
				break;
			case 4:
				// out parameter.
				outParameterList.add(parameter);
				setOutParameter(parameter, dto);
				break;
			case 5:
				// procedure return value.
				setProcedureReturnParameter(parameter, dto);
			}
		}
		System.out.println("SQL:" + csmt.toString());
		try {
			csmt.execute();
			for (OracleStoredProcedureParameter parameter : outParameterList) {
				String parameterName = parameter.getColumnName();
				Object obj = null;
				obj = csmt.getObject(parameterName);
				setObject(parameter, dto, obj);
			}
		} catch (SQLException ex) {

			rollback();

			throw new SQLException(ex);

		}

	}

	private void setProcedureReturnParameter(
			OracleStoredProcedureParameter parameter, Object dto) {
		// TODO Auto-generated method stub

	}

	private void setOutParameter(OracleStoredProcedureParameter parameter,
			Object dto) {

		if(parameter.getDataType()!=1111) {
			
			try {
				csmt.registerOutParameter(parameter.getColumnName(), parameter
						.getDataType(), parameter.getTypeName());
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		} else {

			try {
				csmt.registerOutParameter(parameter.getColumnName(), OracleTypes.CURSOR, parameter.getTypeName());
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	private void setResultParameter(OracleStoredProcedureParameter parameter,
			Object dto) {
		// TODO Auto-generated method stub

	}

	private void setInOutParameter(OracleStoredProcedureParameter parameter,
			Object dto) {
		// TODO Auto-generated method stub

	}

	private void setInParameter(OracleStoredProcedureParameter parameter,
			Object dto) {
		Object obj = getObject(parameter, dto);
		try {
			csmt.setObject(parameter.getColumnName(), obj, parameter
					.getDataType());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Override
	public void execute(Object[] dtoList) throws SQLException {

		try {

			for (Object dto : dtoList) {

				ArrayList<OracleStoredProcedureParameter> outParameterList = new ArrayList<OracleStoredProcedureParameter>();
				for (OracleStoredProcedureParameter parameter : osp
						.getParameterList()) {

					// String parameterName = parameter.getColumnName();
					short parameterType = parameter.getColumnType();
					// int parameterDataType = parameter.getDataType();
					// String parameterDataTypeName = parameter.getTypeName();

					switch (parameterType) {

					case 0:
						throw new RuntimeException(
								"Unknown type procedure column.");
					case 1:
						// in parameter.
						setInParameter(parameter, dto);
						break;
					case 2:
						// inout parameter.
						setInOutParameter(parameter, dto);
						break;
					case 3:
						// result parameter in result set.
						setResultParameter(parameter, dto);
						break;
					case 4:
						// out parameter.
						outParameterList.add(parameter);
						setOutParameter(parameter, dto);
						break;
					case 5:
						// procedure return value.
						setProcedureReturnParameter(parameter, dto);
					}
				}

				System.out.println("SQL:" + csmt.toString());

				csmt.execute();
				for (OracleStoredProcedureParameter parameter : outParameterList) {
					String parameterName = parameter.getColumnName();
					Object obj = null;
					obj = csmt.getObject(parameterName);
					setObject(parameter, dto, obj);
				}

			}

		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			throw new SQLException(e);
		}

	}

	@Override
	public String getCatalog() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String getProcedureNamePattern() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String getSchemaPattern() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void rollback() {
		try {
			if (conn != null) {
				conn.rollback();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	@Override
	protected void finalize() throws Throwable {
		super.finalize();
		DaoUtil.close(csmt);
		DaoUtil.close(conn);
	}

	@Override
	public void close() {
		DaoUtil.close(csmt);
		DaoUtil.close(conn);
	}
}
